package com.haierp.util;

import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class AnalysisExcelUtil {
	public static List<List<Object>> analysisExcel(String fileUrl) {
		List<List<Object>> allRows = new ArrayList<List<Object>>();
		InputStream is = null;
		Workbook wb = null;
		try {
			URL url = new URL(fileUrl);
			URLConnection conn = url.openConnection();
			conn.setConnectTimeout(3000);
			conn.setReadTimeout(3 * 60 * 1000);
			is = conn.getInputStream();
			wb = WorkbookFactory.create(is);
			Sheet sheet = wb.getSheetAt(0);
			int maxRowNum = sheet.getLastRowNum();
			int minRowNum = sheet.getFirstRowNum();
			// 跳过头，从第二行开始读取
			for (int i = minRowNum + 1; i <= maxRowNum; i++) {
				Row row = sheet.getRow(i);
				if (row == null) {
					continue;
				}
				List<Object> rowData = readLine(row);
				if (rowData.get(0).toString() != null && StringUtils.isNotBlank(rowData.get(0).toString())) { //去掉excel空数据行
					allRows.add(rowData);
				}
			}
		} catch (Exception e) {
			try {
				throw new IOException(e);
			} catch (IOException e1) {
				e1.printStackTrace();
			}
		} finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return allRows;
	}
	// 读取每行数据
	private static List<Object> readLine(Row row) {
		short minColNum = row.getFirstCellNum();
		short maxColNum = row.getLastCellNum();
		List<Object> dataList = new ArrayList<Object>();
		for (short colIndex = minColNum; colIndex < maxColNum; colIndex++) {
			Cell cell = row.getCell(colIndex);
			if (cell == null) {
				dataList.add(null);
				continue;
			}
			int cellType = cell.getCellType();
			Object value = null;
			if (Cell.CELL_TYPE_NUMERIC == cellType) {
				value = cell.getNumericCellValue();
			} else if (Cell.CELL_TYPE_STRING == cellType) {
				value = cell.getStringCellValue();
			} else {
				value = cell.getStringCellValue();
			}
			dataList.add(value);
		}
		return dataList;
	}
}
